/* ================================================== */
/* Sample code for a simple extract, transform and    */
/* load of a dimension table.                         */
/*                                                    */
/* The sample code builds on the dimensional model    */
/* previously implemented and populated for the       */
/* BILKA case.                                        */
/*                                                    */
/* Setup SQL for the BILKA case is in a separate      */
/* file, Setup for Sample ETL.sql                     */
/*                                                    */
/*                          Bo Brunsgaard, March 2012 */
/* ================================================== */


-- since the operational systems are normalized
-- and the dimension of product is not, we
-- initially construct a copy table by
-- joining the operational system tables into
-- a denormalized, flattened, structure
drop table productCopy
;
create table productCopy as
select  p.name
      , p.brand
      , p.type
      , s.subcategory
      , c.category
  from  product p
  join  type    t
   on   (t.type = p.type)
  join  subcategory s
   on   (s.subcategory = t.subcategory)
  join  category c
   on   (c.category = s.category) 
;

-- a good idea is to check your data after
-- extracting, just to ensure that invalid
-- data didn't mess with you

select  noOfProducts
      , noAfterJoin
  from  ( 
           ( select count(*) as noOfProducts
             from product)  p
          cross join
           ( select count(*) as noAfterJoin
             from productCopy)  pc
        )
 ;
 
 -- hmm, there seems to be an issue. We have more
 -- products than we get out of the join
 --
 -- let's check the contents of product
 
 select substr(name,1,20) as name
      , substr(brand,1,20) as brand
      , substr(type,1,20) as type
  from product
  ;
  
-- is the type (unknown) defined?

 select substr(name,1,20) as name
      , substr(brand,1,20) as brand
      , substr(type,1,20) as type
  from product
  where not type in (
                      select type
                        from type
                    )
;

-- ==================================
-- --------------------------------
-- OK - decision time!
-- --------------------------------
-- ===================================


drop table extractError001
;
create table extractError001
 as
   select name
        , brand
        , type
        , to_char(sysdate,'yyyy-mm-dd') || ' not extracted - bad data' as reason
     from product p
    where not exists (
                        select 'x'
                          from productCopy pc
                          where pc.name = p.name
                           and  pc.brand = p.brand
                     )
  ;

 select *
  from  extractError001
;

-- ======================================
-- OK, at least we have a copy
-- 
-- Now we need to introduce some changes
-- ======================================

update product
  set  type = 'vegetable'
  where name = 'Ketchup'
;

insert
  into product (name,brand,type)
  values (
             'Banana'
           , 'Costa Rica'
           , 'vegetable'
         )
;

-- aahh, by the way, banana is a fruit, not a vegetable.
-- well, what the heck

delete 
  from product
 where name = 'Pork Chops'
  and  brand = 'Danish Crown'
;
commit
;

-- =====================================
-- We can find rows that are changed or added
-- simply by excluding those that are
-- the same as in the copy
-- =====================================

select  p.name
      , p.brand
      , p.type
      , s.subcategory
      , c.category
  from  product p
  join  type    t
   on   (t.type = p.type)
  join  subcategory s
   on   (s.subcategory = t.subcategory)
  join  category c
   on   (c.category = s.category)
minus
select  pc.name
      , pc.brand
      , pc.type
      , pc.subcategory
      , pc.category
  from  productCopy pc
;

-- -------------------------------
-- however, we will need to distinguish 
-- changed from added
--
-- Added rows must be the ones where
-- the primary key (name + brand) is
-- in the current product table and
-- NOT in the old copy
-- ------------------------------

select  'added' as operation
      , p.name
      , p.brand
      , p.type
      , s.subcategory
      , c.category
  from  product p
  join  type    t
   on   (t.type = p.type)
  join  subcategory s
   on   (s.subcategory = t.subcategory)
  join  category c
   on   (c.category = s.category)
  where exists (
                  select 'x'
                    from ( select  new.name
                                 , new.brand
                              from product new
                          minus
                           select  old.name
                                 , old.brand
                              from productCopy old
                          ) addedKeys
                    where p.name = addedKeys.name
                     and  p.brand = addedkeys.brand
                )
;

-- ---------------------------
-- Thus changed rows must be those that
-- are not equal in the present and the copy
-- and which are NOT added
-- -----------------------------
select  'changed' as operation
      , name
      , brand
      , type
      , subcategory
      , category
  from  (
           select p.name
                , p.brand
                , p.type
                , s.subcategory
                , c.category
             from product p
             join  type    t
              on   (t.type = p.type)
             join  subcategory s
              on   (s.subcategory = t.subcategory)
             join  category c
              on   (c.category = s.category)
           minus
             select  pc.name
                   , pc.brand
                   , pc.type
                   , pc.subcategory
                   , pc.category
               from  productCopy pc
          ) changed
  where not exists (
                  select 'x'
                    from ( select  new.name
                                 , new.brand
                              from product new
                          minus
                           select  old.name
                                 , old.brand
                              from productCopy old
                          ) addedKeys
                    where changed.name = addedKeys.name
                     and  changed.brand = addedkeys.brand
                )
;

-- -------------------------------------------
-- deleted rows must be the reverse of the added
-- ie. those primary keys that are in the old copy
-- but not in the present products table
-- ---------------------------------------------


select  'deleted' as operation
      , p.name
      , p.brand
      , p.type
      , p.subcategory
      , p.category
  from  productCopy p
  where exists (
                  select 'x'
                    from ( select  old.name
                                 , old.brand
                              from productCopy old
                          minus
                           select  new.name
                                 , new.brand
                              from product new
                          ) deletedKeys
                    where p.name = deletedKeys.name
                     and  p.brand = deletedkeys.brand
                )
;

-- ----------------------------------------
-- So, we can now create an extract table
-- of all changes that we must carry on
-- over in our dimension
-- ---------------------------------------

drop table changes
;
create table changes as

select  'added' as operation
      , p.name
      , p.brand
      , p.type
      , s.subcategory
      , c.category
  from  product p
  join  type    t
   on   (t.type = p.type)
  join  subcategory s
   on   (s.subcategory = t.subcategory)
  join  category c
   on   (c.category = s.category)
  where exists (
                  select 'x'
                    from ( select  new.name
                                 , new.brand
                              from product new
                          minus
                           select  old.name
                                 , old.brand
                              from productCopy old
                          ) addedKeys
                    where p.name = addedKeys.name
                     and  p.brand = addedkeys.brand
                )

union all

select  'changed' as operation
      , name
      , brand
      , type
      , subcategory
      , category
  from  (
           select p.name
                , p.brand
                , p.type
                , s.subcategory
                , c.category
             from product p
             join  type    t
              on   (t.type = p.type)
             join  subcategory s
              on   (s.subcategory = t.subcategory)
             join  category c
              on   (c.category = s.category)
           minus
             select  pc.name
                   , pc.brand
                   , pc.type
                   , pc.subcategory
                   , pc.category
               from  productCopy pc
          ) changed
  where not exists (
                  select 'x'
                    from ( select  new.name
                                 , new.brand
                              from product new
                          minus
                           select  old.name
                                 , old.brand
                              from productCopy old
                          ) addedKeys
                    where changed.name = addedKeys.name
                     and  changed.brand = addedkeys.brand
                )

union all

select  'deleted' as operation
      , p.name
      , p.brand
      , p.type
      , p.subcategory
      , p.category
  from  productCopy p
  where exists (
                  select 'x'
                    from ( select  old.name
                                 , old.brand
                              from productCopy old
                          minus
                           select  new.name
                                 , new.brand
                              from product new
                          ) deletedKeys
                    where p.name = deletedKeys.name
                     and  p.brand = deletedkeys.brand
                )
;

select *
 from changes
 ;
 
 
 -- =================================
 -- For this part, we have actually already
 -- done the required transformations, so
 -- we can go straight to updating the
 -- dimension
 -- ==================================
 
 -- first added rows
 
 insert
   into dim_product (id,name,brand,type,subcategory, category,validFrom,validTo,RandomPick)
   select sqDWH.nextval
         , name
         , brand
         , type
         , subcategory
         , category
         , trunc(sysdate,'DDD')
         , to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
         , 999
      from changes
      where operation = 'added'
      ;
      
select *
 from  dim_product
 ;
 
-- ------------------------------------
-- deleted rows must be updated, setting their
-- validTo at the end of the period BEFORE
-- the loading of the current extract
--
-- This, of course, only applies to the currently
-- valid entry in the dimension!
-- ----------------------------------------

update dim_product dp
  set  dp.validTo =  trunc(sysdate,'DDD') - to_dsInterval('000 00:00:01')
  where exists (
                   select   'x'
                     from   changes c
                    where   c.name = dp.name
                     and    c.brand = dp.brand
                     and    c.operation = 'deleted'
                )
   and   dp.validTo = to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
;                     

select *
 from  dim_product
 ;
rollback;


update dim_product dp
  set  dp.validTo =  trunc(sysdate,'DDD') - to_dsInterval('000 00:00:01')
  where exists (
                   select   'x'
                     from   changes c
                    where   c.name = dp.name
                     and    c.brand = dp.brand
                     and    c.operation = 'changed'
                )
   and   dp.validTo = to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
;                     

insert
   into dim_product (id,name,brand,type,subcategory, category,validFrom,validTo,RandomPick)
   select sqDWH.nextval
         , name
         , brand
         , type
         , subcategory
         , category
         , trunc(sysdate,'DDD')
         , to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS')
         , 999
      from changes
      where operation = 'changed'
      ;

select *
 from  dim_product
;
rollback
;
-- ==============================
-- Having updated the dimension, we
-- of course need to refreshen the
-- contents of the copy table, so it
-- will now represent what is in the
-- database at present, thereby
-- enabling us to detect changes made
-- between now and the next extract
-- ==================================

drop table productCopy
;
create table productCopy as
select  p.name
      , p.brand
      , p.type
      , s.subcategory
      , c.category
  from  product p
  join  type    t
   on   (t.type = p.type)
  join  subcategory s
   on   (s.subcategory = t.subcategory)
  join  category c
   on   (c.category = s.category) 
;



-- ===================================
-- OK, that was one dimension
-- now we just need  to do the others :-)
-- ==================================